import { DataTypes, Model, Op } from 'sequelize';
import sequelize from '../utils/database';
import { User } from './User';
import OrderItem from './OrderItem';

export interface OrderAttributes {
  id?: number;
  orderNo: string;
  userId: number;
  orderStatus?: number; // 1-待付款，2-待发货，3-待收货，4-已完成，5-已取消，6-退款中
  paymentStatus?: number; // 0-未支付，1-已支付
  shippingStatus?: number; // 0-未发货，1-已发货，2-已收货
  consigneeName: string;
  consigneePhone: string;
  consigneeAddress: string;
  totalAmount: number;
  goodsAmount: number;
  shippingFee?: number;
  pointsAmount?: number;
  paymentMethod?: number; // 1-支付宝，2-微信，3-银行卡
  paymentTime?: Date;
  shippingTime?: Date;
  confirmTime?: Date;
  deliveryCompany?: string;
  deliveryNo?: string;
  buyerMessage?: string;
  sellerRemark?: string;
  autoConfirmDay?: number;
  createdAt?: Date;
  updatedAt?: Date;
}

export class OrderSequelize extends Model<OrderAttributes> implements OrderAttributes {
  public id!: number;
  public orderNo!: string;
  public userId!: number;
  public orderStatus!: number;
  public paymentStatus!: number;
  public shippingStatus!: number;
  public consigneeName!: string;
  public consigneePhone!: string;
  public consigneeAddress!: string;
  public totalAmount!: number;
  public goodsAmount!: number;
  public shippingFee!: number;
  public pointsAmount!: number;
  public paymentMethod?: number;
  public paymentTime?: Date;
  public shippingTime?: Date;
  public confirmTime?: Date;
  public deliveryCompany?: string;
  public deliveryNo?: string;
  public buyerMessage?: string;
  public sellerRemark?: string;
  public autoConfirmDay!: number;
  public readonly createdAt!: Date;
  public readonly updatedAt!: Date;

  // 关联的用户信息
  public user?: User;
  
  // 关联的订单商品项
  public items?: OrderItem[];

  // 实例方法
  public getOrderStatusText(): string {
    const statusMap = {
      1: '待付款',
      2: '待发货', 
      3: '待收货',
      4: '已完成',
      5: '已取消',
      6: '退款中'
    };
    return statusMap[this.orderStatus as keyof typeof statusMap] || '未知';
  }

  public getPaymentStatusText(): string {
    return this.paymentStatus === 1 ? '已支付' : '未支付';
  }

  public getShippingStatusText(): string {
    const statusMap = {
      0: '未发货',
      1: '已发货',
      2: '已收货'
    };
    return statusMap[this.shippingStatus as keyof typeof statusMap] || '未知';
  }

  // 静态方法：分页查询订单列表
  static async findAllWithPagination(params: {
    page?: number;
    limit?: number;
    orderNo?: string;
    userId?: number;
    orderStatus?: number;
    paymentStatus?: number;
    shippingStatus?: number;
    startDate?: string;
    endDate?: string;
    consigneeName?: string;
    consigneePhone?: string;
    sort?: string;
    order?: 'ASC' | 'DESC';
  }) {
    const {
      page = 1,
      limit = 20,
      orderNo,
      userId,
      orderStatus,
      paymentStatus,
      shippingStatus,
      startDate,
      endDate,
      consigneeName,
      consigneePhone,
      sort = 'createdAt',
      order = 'DESC'
    } = params;

    const offset = (page - 1) * limit;

    // 构建查询条件
    const whereCondition: any = {};

    if (orderNo) {
      whereCondition.orderNo = {
        [Op.like]: `%${orderNo}%`
      };
    }

    if (userId) {
      whereCondition.userId = userId;
    }

    if (orderStatus !== undefined) {
      whereCondition.orderStatus = orderStatus;
    }

    if (paymentStatus !== undefined) {
      whereCondition.paymentStatus = paymentStatus;
    }

    if (shippingStatus !== undefined) {
      whereCondition.shippingStatus = shippingStatus;
    }

    if (startDate || endDate) {
      whereCondition.createdAt = {};
      if (startDate) {
        whereCondition.createdAt[Op.gte] = new Date(startDate);
      }
      if (endDate) {
        whereCondition.createdAt[Op.lte] = new Date(endDate + ' 23:59:59');
      }
    }

    if (consigneeName) {
      whereCondition.consigneeName = {
        [Op.like]: `%${consigneeName}%`
      };
    }

    if (consigneePhone) {
      whereCondition.consigneePhone = {
        [Op.like]: `%${consigneePhone}%`
      };
    }

    // 安全的排序字段白名单
    const allowedSortFields = ['id', 'orderNo', 'createdAt', 'updatedAt', 'totalAmount', 'orderStatus'];
    const safeSort = allowedSortFields.includes(sort) ? sort : 'createdAt';

    const { count, rows } = await OrderSequelize.findAndCountAll({
      where: whereCondition,
      include: [
        {
          model: User,
          as: 'user',
          attributes: ['id', 'username', 'phone', 'email']
        },
        {
          model: OrderItem,
          as: 'items',
          attributes: ['id', 'productId', 'skuId', 'productName', 'productImage', 'skuSpecifications', 'price', 'quantity', 'totalAmount']
        }
      ],
      order: [[safeSort, order]],
      limit,
      offset,
      distinct: true
    });

    return {
      orders: rows,
      total: count,
      page,
      limit,
      totalPages: Math.ceil(count / limit)
    };
  }
}

OrderSequelize.init(
  {
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true,
      comment: '订单ID'
    },
    orderNo: {
      type: DataTypes.STRING(32),
      allowNull: false,
      unique: true,
      comment: '订单号',
      field: 'order_no'
    },
    userId: {
      type: DataTypes.INTEGER,
      allowNull: false,
      comment: '用户ID',
      field: 'user_id'
    },
    orderStatus: {
      type: DataTypes.TINYINT,
      allowNull: false,
      defaultValue: 1,
      comment: '订单状态：1-待付款，2-待发货，3-待收货，4-已完成，5-已取消，6-退款中',
      field: 'order_status'
    },
    paymentStatus: {
      type: DataTypes.TINYINT,
      allowNull: false,
      defaultValue: 0,
      comment: '支付状态：0-未支付，1-已支付',
      field: 'payment_status'
    },
    shippingStatus: {
      type: DataTypes.TINYINT,
      allowNull: false,
      defaultValue: 0,
      comment: '物流状态：0-未发货，1-已发货，2-已收货',
      field: 'shipping_status'
    },
    consigneeName: {
      type: DataTypes.STRING(50),
      allowNull: false,
      comment: '收货人姓名',
      field: 'consignee_name'
    },
    consigneePhone: {
      type: DataTypes.STRING(20),
      allowNull: false,
      comment: '收货人电话',
      field: 'consignee_phone'
    },
    consigneeAddress: {
      type: DataTypes.STRING(500),
      allowNull: false,
      comment: '收货地址',
      field: 'consignee_address'
    },
    totalAmount: {
      type: DataTypes.DECIMAL(10, 2),
      allowNull: false,
      comment: '订单总金额',
      field: 'total_amount'
    },
    goodsAmount: {
      type: DataTypes.DECIMAL(10, 2),
      allowNull: false,
      comment: '商品总金额',
      field: 'goods_amount'
    },
    shippingFee: {
      type: DataTypes.DECIMAL(8, 2),
      allowNull: false,
      defaultValue: 0.00,
      comment: '运费',
      field: 'shipping_fee'
    },
    pointsAmount: {
      type: DataTypes.DECIMAL(8, 2),
      allowNull: false,
      defaultValue: 0.00,
      comment: '积分抵扣金额',
      field: 'points_amount'
    },
    paymentMethod: {
      type: DataTypes.TINYINT,
      allowNull: true,
      comment: '支付方式：1-支付宝，2-微信，3-银行卡',
      field: 'payment_method'
    },
    paymentTime: {
      type: DataTypes.DATE,
      allowNull: true,
      comment: '支付时间',
      field: 'payment_time'
    },
    shippingTime: {
      type: DataTypes.DATE,
      allowNull: true,
      comment: '发货时间',
      field: 'shipping_time'
    },
    confirmTime: {
      type: DataTypes.DATE,
      allowNull: true,
      comment: '确认收货时间',
      field: 'confirm_time'
    },
    deliveryCompany: {
      type: DataTypes.STRING(50),
      allowNull: true,
      comment: '快递公司',
      field: 'delivery_company'
    },
    deliveryNo: {
      type: DataTypes.STRING(100),
      allowNull: true,
      comment: '快递单号',
      field: 'delivery_no'
    },
    buyerMessage: {
      type: DataTypes.STRING(500),
      allowNull: true,
      comment: '买家留言',
      field: 'buyer_message'
    },
    sellerRemark: {
      type: DataTypes.STRING(500),
      allowNull: true,
      comment: '卖家备注',
      field: 'seller_remark'
    },
    autoConfirmDay: {
      type: DataTypes.TINYINT,
      allowNull: false,
      defaultValue: 7,
      comment: '自动确认收货天数',
      field: 'auto_confirm_day'
    }
  },
  {
    sequelize,
    tableName: 'orders',
    modelName: 'OrderSequelize',
    timestamps: true,
    underscored: true,
    comment: '订单表',
    indexes: [
      {
        name: 'idx_order_no',
        fields: ['order_no']
      },
      {
        name: 'idx_user_id',
        fields: ['user_id']
      },
      {
        name: 'idx_order_status',
        fields: ['order_status']
      },
      {
        name: 'idx_payment_status',
        fields: ['payment_status']
      },
      {
        name: 'idx_created_at',
        fields: ['created_at']
      }
    ]
  }
);

// 定义关联关系
OrderSequelize.belongsTo(User, {
  foreignKey: 'userId',
  as: 'user'
});

OrderSequelize.hasMany(OrderItem, {
  foreignKey: 'orderId',
  as: 'items'
});

OrderItem.belongsTo(OrderSequelize, {
  foreignKey: 'orderId',
  as: 'order'
});

export default OrderSequelize;